package com.adcc.eoss.cawebservice.domain;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.internal.util.StringHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.adcc.eoss.cawebservice.StringUtil;

@Repository
public class FlightPlanRepository {

	protected final Logger log = LoggerFactory.getLogger(getClass());

	@Autowired
	protected JdbcTemplate jdbc;

	@Transactional(readOnly = true)
	public synchronized List<CrewSchedule> getFlightPlan(String pilotId, String own, String std, String sta,
			String dep, String arr, String flt, String fdt, String short_reg, String beginDate, String endDate,
			String arn) {
		try {
			List<CrewSchedule> crewSchedules = new ArrayList<CrewSchedule>();
			StringBuilder sql = new StringBuilder();
			sql.append("SELECT A.FDT,A.CRR,A.FLT,A.ACT,A.ARN,A.DEP,A.STD,A.ARR,A.STA,A.VHS,A.WTP,A.TSK,A.PSC,A.PSN,A.PSP,A.PSR,A.PSD,"
					+ "A.PST,A.OWN,A.TSKS,A.DHS,A.TSIX,A.RTYPE,A.VALT,A.BSE,B.ID,B.CODE,B.NAME"
					+ ",B.DEPTID,B.ID_CARD,B.GENDER,B.SUBTEAM,B.OWN,B.COCKPIT,B.VALT,B.PHR"
					+ " FROM CREWSCHEDULE A LEFT JOIN dd_da_archive_smp B ON A.PSC = B.ID AND A.OWN = B.OWN WHERE 1=1");
			if (!StringHelper.isEmpty(pilotId)) {
				sql.append(" AND A.PSC = " + pilotId);
			}
			if (!StringHelper.isEmpty(own)) {
				sql.append(" AND A.OWN = '" + own + "'");
			}
			if (!StringHelper.isEmpty(std)) {
				sql.append(" AND A.STD = CAST('" + std + "' AS DATETIME)");
			}
			if (!StringHelper.isEmpty(sta)) {
				sql.append(" AND A.STA = CAST('" + sta + "' AS DATETIME)");
			}
			if (!StringHelper.isEmpty(dep)) {
				sql.append(" AND A.DEP = '" + dep + "'");
			}
			if (!StringHelper.isEmpty(arr)) {
				sql.append(" AND A.ARR = '" + arr + "'");
			}
			if (!StringHelper.isEmpty(flt)) {
				sql.append(" AND A.FLT = '" + flt + "'");
			}
			if (!StringHelper.isEmpty(fdt)) {
				sql.append(" AND A.FDT = CAST('" + fdt + "' AS DATETIME)");
			}
			if (!StringHelper.isEmpty(short_reg)) {
				sql.append(" AND A.ACT = '" + short_reg + "'");
			}
			if (!StringHelper.isEmpty(beginDate)) {
				sql.append(" AND A.STD >= CAST('" + beginDate + "' AS DATETIME)");
			}
			if (!StringHelper.isEmpty(endDate)) {
				sql.append(" AND A.STD < CAST('" + endDate + "' AS DATETIME)");
			}
			if (!StringHelper.isEmpty(arn)) {
				sql.append(" AND A.ARN = '" + arn + "'");
			}
			sql.append(" ORDER BY A.FDT,A.FLT,A.DEP,A.ARR");
			List<Map<String, Object>> list = jdbc.queryForList(sql.toString());
			Iterator<Map<String, Object>> iterator = list.iterator();
			CrewSchedule crewSchedule = null;
			FlightPlan flightPlan = null;
			Pilot pilot = null;
			List<Pilot> pilots = null;
			String primaryKey = "";
			String nowKey = "";
			while (iterator.hasNext()) {
				Map<String, Object> mapCrewSchedule = (Map<String, Object>) iterator.next();
				nowKey = (Date) mapCrewSchedule.get("FDT") + (String) mapCrewSchedule.get("DEP")
						+ (String) mapCrewSchedule.get("ARR") + (String) mapCrewSchedule.get("FLT");
				if (!primaryKey.equals(nowKey)) {
					if (crewSchedule != null) {
						crewSchedule.setPilots(pilots);
						crewSchedules.add(crewSchedule);
					}
					crewSchedule = new CrewSchedule();
					flightPlan = new FlightPlan();
					pilots = new ArrayList<Pilot>();
					flightPlan.setAct((String) mapCrewSchedule.get("ACT"));
					flightPlan.setArn((String) mapCrewSchedule.get("ARN"));
					flightPlan.setBse((String) mapCrewSchedule.get("BSE"));
					flightPlan.setCrr((String) mapCrewSchedule.get("CRR"));
					flightPlan.setDhs((String) mapCrewSchedule.get("DHS").toString());
					flightPlan.setRtype((String) mapCrewSchedule.get("RTYPE"));
					flightPlan.setSta(StringUtil.DateToString((Date) mapCrewSchedule.get("STA")));
					flightPlan.setStd(StringUtil.DateToString((Date) mapCrewSchedule.get("STD")));
					flightPlan.setTsix((Integer) mapCrewSchedule.get("TSIX"));
					flightPlan.setTsk((String) mapCrewSchedule.get("TSK"));
					flightPlan.setTsks((Integer) mapCrewSchedule.get("TSKS"));
					flightPlan.setValt(StringUtil.DateToString((Date) mapCrewSchedule.get("VALT")));
					flightPlan.setVhs((BigDecimal) mapCrewSchedule.get("VHS"));
					flightPlan.setWtp((String) mapCrewSchedule.get("WTP"));
					crewSchedule.setArr((String) mapCrewSchedule.get("ARR"));
					crewSchedule.setDep((String) mapCrewSchedule.get("DEP"));
					crewSchedule.setFdt(StringUtil.DateToString((Date) mapCrewSchedule.get("FDT")));
					crewSchedule.setFlt((String) mapCrewSchedule.get("FLT"));
					crewSchedule.setFlightPlan(flightPlan);
				}
				pilot = new Pilot();
				pilot.setId((BigDecimal) mapCrewSchedule.get("id"));
				pilot.setCockpit((Integer) mapCrewSchedule.get("cockpit"));
				pilot.setCode((String) mapCrewSchedule.get("code"));
				pilot.setDeptid((String) mapCrewSchedule.get("deptid"));
				pilot.setGender((Integer) mapCrewSchedule.get("gender"));
				pilot.setId_card((String) mapCrewSchedule.get("id_card"));
				pilot.setName((String) mapCrewSchedule.get("name"));
				pilot.setOwn((String) mapCrewSchedule.get("own"));
				pilot.setPhr((String) mapCrewSchedule.get("phr"));
				pilot.setSubteam((String) mapCrewSchedule.get("subteam"));
				pilot.setValt(StringUtil.DateToString((Date) mapCrewSchedule.get("valt")));
				pilot.setPsp((BigDecimal) mapCrewSchedule.get("psp"));
				pilots.add(pilot);
				primaryKey = nowKey;
			}
			if (crewSchedule != null) {
				crewSchedule.setPilots(pilots);
				crewSchedules.add(crewSchedule);
			}
			return crewSchedules;
		} catch (Exception ex) {
			log.error(ex.getMessage());
			return null;
		}
	}

}